package org.hibiscus.jdp.app.dbdoc;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Description: 抽象SQL工具类
 *
 * @author 然诺
 * @date 2019/08/12
 */
public abstract class AbstractSqlUtils {
	private static final Logger log = LoggerFactory.getLogger(AbstractSqlUtils.class);

	/**
	 * 获取连接
	 */
	public static Connection getConnection(String url, String user, String password) {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			String msg = "获取数据库连接异常！msg:" + e.getMessage();
			log.error(msg, e);
			throw new RuntimeException(msg);
		}
	}

	/**
	 * 关闭Connection
	 */
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error("关闭Connection异常", e);
			}
		}
	}

	/**
	 * 关闭Statement
	 */
	public static void closeStatement(Statement stmt) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				log.error("关闭Statement异常", e);
			}
		}
	}

	/**
	 * 关闭ResultSet
	 */
	public static void closeResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				log.error("关闭ResultSet异常", e);
			}
		}
	}

	/**
	 * 执行SQL
	 *
	 * @param conn
	 * @param sql
	 * @return
	 */
	public static ResultSet executeQuery(Connection conn, String sql) {
		Statement statement = null;
		try {
			statement = conn.createStatement();
			return statement.executeQuery(sql);
		} catch (SQLException e) {
			log.error("创建执行SQL异常", e);
			try {
				if (statement != null) {
					statement.close();
				}
				closeConnection(conn);
			} catch (SQLException e1) {
				log.error("关闭连接异常", e1);
			}
			return null;
		}
	}

	protected static List<Map<String, String>> getTableStructureData(String url, String user, String password, String sql, boolean isMetaInfo) {
		List<Map<String, String>> ret = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection(url, user, password);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			ret = isMetaInfo ? convertToTableMetaInfo(rs) : convertToRowRenderData(rs);
		} catch (Exception e) {
			log.error("SQL执行异常", e);
		} finally {
			closeStatement(stmt);
			closeConnection(conn);
			closeResultSet(rs);
		}
		return ret;
	}

	/**
	 * 获取数据库的所有表名及表的信息
	 *
	 * @param rs
	 * @return list
	 */
	public static List<Map<String, String>> convertToTableMetaInfo(ResultSet rs) {
		if (rs == null) {
			return new ArrayList<>();
		}
		List<Map<String, String>> list = new ArrayList<>();
		try {
			while (rs.next()) {
				Map<String, String> result = new HashMap<>(6);
				result.put("table_name", rs.getString("table_name"));
				result.put("table_type", rs.getString("table_type"));
				result.put("engine", rs.getString("engine"));
				result.put("table_collation", rs.getString("table_collation"));
				result.put("table_comment", rs.getString("table_comment"));
				result.put("create_options", rs.getString("create_options"));
				list.add(result);
			}
		} catch (SQLException e) {
			log.error("ResultSet数据获取异常", e);
		}
		return list;
	}

	/**
	 * 获取数据库的所有表名及表的信息
	 *
	 * @param rs
	 * @return list
	 */
	public static List<Map<String, String>> convertToRowRenderData(ResultSet rs) {
		if (rs == null) {
			return new ArrayList<>();
		}
		List<Map<String, String>> list = new ArrayList<>();
		try {
			while (rs.next()) {
				Map<String, String> result = new HashMap<>(6);
				result.put("ordinal_position", rs.getString("ordinal_position"));
				result.put("column_name", rs.getString("column_name"));
				result.put("column_type", rs.getString("column_type"));
				result.put("column_comment", rs.getString("column_comment"));
				result.put("is_nullable", rs.getString("is_nullable"));
				result.put("column_default", rs.getString("column_default"));
				list.add(result);
			}
		} catch (SQLException e) {
			log.error("ResultSet数据获取异常", e);
		}
		return list;
	}

}
